﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace Test.DAL
{
   /* sql防注入
    select*from info where name='1001 or 1=1' and pwd=123456
    1.屏蔽特殊字符
     2.参数化
    */
   /*
      using
        1.引用命名空间
        2.垃圾自动回收
        3.命名空间别名
    */
    /// </summary>
    class SqlHelper
    {
        //1.创建数据库连接字符串
        private static string strConn = ConfigurationManager.ConnectionStrings["TestConnectionstring"].ConnectionString;
        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="sql">查询SQL语句</param>
        /// <param name="sqlParameters"></param>
        /// <returns>参数列表</returns>
        public static DataTable Query(string sql,params SqlParameter[] sqlParameters)
        {
            //2.创建新的DataTable对象，准备用来存储数据
            DataTable table = new DataTable();
            //3.创建dta对象，也是固定的，后面两个参数，第一个sql是指的sql语句，后者是数据库链接字符串
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sql,strConn);
            //4.判断sql语句是否执行成功并查询到至少一条数据
            if (sqlParameters.Length > 0)
            {
                //5.还是把数据放到了dataset对象里面，也就是table里
                dataAdapter.SelectCommand.Parameters.AddRange(sqlParameters);
            }
            //6.调用dataadapter的fill方法
            dataAdapter.Fill(table);
            //7.返回table
            return table;
        }
        /// <summary>
        /// 单条查询
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="sqlParameters"></param>
        /// <returns></returns>
        public static SqlDataReader Reader(string sql,params SqlParameter[] sqlParameters)
        {
            //创建conn对象
            SqlConnection sqlConnection = new SqlConnection(strConn);
            //打开数据库
            sqlConnection.Open();
            //创建comm对象
            SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection);
            if (sqlParameters.Length>0)
            {
                sqlCommand.Parameters.AddRange(sqlParameters);
            }
            //关闭数据库
            SqlDataReader dataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
            //dataReader.close
            return dataReader;
        }
        /// <summary>
        /// 非查询
        /// </summary>
        /// <param name="sql">查询SQL语句</param>
        /// <param name="sqlParameters"></param>
        /// <returns>参数列表</returns>
        public static int NonQuery(string sql, params SqlParameter[] sqlParameters)
        {
            int num = 0;
            //创建新的DataTable对象，准备用来存储数据
            using (SqlConnection sqlconnection = new SqlConnection(strConn))
            {
                //创建comm对象
                using (SqlCommand sqlCommand = new SqlCommand(sql, sqlconnection))
                {
                    try
                    {
                        //打开数据库
                        sqlconnection.Open();
                        if (sqlParameters.Length > 0)
                        {
                            sqlCommand.Parameters.AddRange(sqlParameters);
                        }
                        //进行数据传输
                        num = sqlCommand.ExecuteNonQuery();
                    }
                    finally
                    {
                        //判断数据库是否处于打开
                        if (sqlconnection.State == ConnectionState.Open)
                        {
                            //关闭数据库
                            sqlconnection.Close();
                        }
                    }
                    //sqlconnection.Dispose();
                    //返回数据
                    return num;
                }
            }
        }
    }
}
